from django.shortcuts import render, redirect
from database import models
from django.http import JsonResponse
from django.views.decorators.http import require_http_methods
from django.db import connections
import json
import requests
from django.views.decorators.csrf import csrf_exempt
import re


def welcome(request):
    return render(request, 'welcome.html')


def login(request):
    if request.method == 'GET':
        return render(request, 'login.html')
    else:
        username = request.POST['user']
        password = request.POST['pwd']
        judge = models.Userinfo.objects.filter(username=username, password=password).first()
        if judge:
            return redirect('/test')
        else:
            return render(request, 'login.html', {"error": "用户名或密码错误"})


def register(request):
    if request.method == 'GET':
        return render(request, 'register.html')
    else:
        phonenumber = request.POST['phone']
        username = request.POST['user']
        password = request.POST['pwd']
        repassword = request.POST['repwd']
        judge = models.Userinfo.objects.filter(phonenumber=phonenumber).first()
        if judge is None and password == repassword:
            models.Userinfo.objects.create(phonenumber=phonenumber, username=username, password=password)
            return redirect('/login/')
        else:
            if judge:
                return render(request, "register.html", {"error": "此手机号已被注册"})
            elif password != repassword:
                return render(request, "register.html", {"error": "两次密码输入不一致"})


def index(request):
    if request.method == 'GET':
        return render(request, 'index.html')
    else:
        command = request.POST['cmd']
        if not is_valid_sql(command):
            return JsonResponse({'error': 'Invalid SQL statement'}, status=400)
        try:
            with connections['default'].cursor() as cursor:
                cursor.execute(command)
                if command.strip().lower().startswith('select'):
                    rows = cursor.fetchall()
                    return JsonResponse({'data': rows}, safe=False)
                else:
                    connections['default'].commit()
                    return JsonResponse({'message': 'SQL executed successfully'})
        except Exception as e:
            return JsonResponse({'error': str(e)}, status=500)


import re

def search(request):
    pass


# 设置百度 API Key 和 Secret Key
API_KEY = 'Amwd2IYuSKyUD17KV2PbyXox'
SECRET_KEY = 'RQ0GCUuay3YPOjaHFa1OYRLQrBU5AKIZ'


# 获取 Access Token
def get_access_token():
    url = f'https://aip.baidubce.com/oauth/2.0/token?grant_type=client_credentials&client_id={API_KEY}&client_secret={SECRET_KEY}'
    response = requests.get(url)
    if response.status_code == 200:
        return response.json().get('access_token')
    else:
        print(f"Failed to get access token: {response.status_code} {response.text}")
    return None


def call_api(query):
    access_token = get_access_token()
    if not access_token:
        return None, 'Failed to get access token'

    url = f'https://aip.baidubce.com/rpc/2.0/ai_custom/v1/wenxinworkshop/chat/yi_34b_chat?access_token={access_token}'
    headers = {'Content-Type': 'application/json'}
    payload = {
        'messages': [
            {"role": "user", "content": query}
        ]
    }
    response = requests.post(url, headers=headers, data=json.dumps(payload))
    print(f"Response status code: {response.status_code}")
    print(f"Response text: {response.text}")
    if response.status_code == 200:
        try:
            return response.json(), None
        except json.JSONDecodeError:
            return response.text, f"Failed to decode JSON: {response.text}"
    else:
        return response.text, f"API request failed: {response.status_code} {response.text}"







@csrf_exempt
def nl_to_sql(request):
    if request.method == 'POST':
        try:
            body = json.loads(request.body)
            natural_language_query = body.get('query', '')
            database_description = (
                "我有一个名为 data_co_polite 的数据库，包含三个表：student_info、courses 和 student_grades。\n\n"
                "student_info 表：\n"
                "student_id：整数型，主键，自动递增\n"
                "name：字符串型，最长100字符\n"
                "gender：枚举类型（male, female）\n"
                "grade：字符串型，最长10字符\n"
                "major：字符串型，最长100字符\n"
                "phone_number：字符串型，最长15字符\n\n"
                "courses 表：\n"
                "course_id：整数型，主键，自动递增\n"
                "course_name：字符串型，最长100字符\n"
                "course_description：文本型\n\n"
                "student_grades 表：\n"
                "grade_id：整数型，主键，自动递增\n"
                "student_id：整数型，外键，引用 student_info(student_id)\n"
                "course_id：整数型，外键，引用 courses(course_id)\n"
                "grade：字符串型，最长2字符\n"
                "semester：字符串型，最长20字符\n\n"
                "表之间的关系如下：\n"
                "student_info 表中的 student_id 是主键。\n"
                "courses 表中的 course_id 是主键。\n"
                "student_grades 表中的 student_id 是外键，引用 student_info(student_id)。\n"
                "student_grades 表中的 course_id 是外键，引用 courses(course_id)。\n\n"
                "我将输入一段自然语言，请你帮我生成对应的SQL语句：\n"
                "请你仅仅返回给我SQL语句，不要有其他额外的话语\n"
                "select和from等词语需要大写,数据库里的表名列名都是小写的,只涉及单表查询且不要出现表明.列名的格式\n"
            )

            # 调用文心一言 API
            response, error = call_api(database_description+natural_language_query)
            if error:
                return JsonResponse({'error': error, 'response': response}, status=400)

            print(f"API response: {response}")

            # 确保 response 是一个字典并获取 result 字段
            if isinstance(response, dict) and 'result' in response:
                sql_query = response['result'].strip()
                # 移除多余的格式化字符
                sql_query = sql_query.replace("```sql", "").replace("```", "").strip()
                return JsonResponse({'sql_query': sql_query})
            else:
                return JsonResponse({'error': 'Unexpected response format', 'response': response}, status=400)
        except json.JSONDecodeError as e:
            return JsonResponse({'error': f"JSON decode error: {str(e)}"}, status=400)
        except Exception as e:
            return JsonResponse({'error': str(e)}, status=400)
    return JsonResponse({'error': 'Invalid request method'}, status=405)


def test(request):
    return render(request, 'test.html')

def result(request):
    if request.method == 'GET':
        return render(request, 'result.html')




def execute_sql(request):
    if request.method == 'POST':
        try:
            body = json.loads(request.body)
            command = body.get('cmd', '')
            if not is_valid_sql(command):
                return JsonResponse({'error': 'Invalid SQL statement'}, status=400)
            try:
                with connections['default'].cursor() as cursor:
                    cursor.execute(command)
                    if command.strip().lower().startswith('select'):
                        columns = [col[0] for col in cursor.description]  # 获取列名
                        data = cursor.fetchall()
                        result = {
                            'columns': columns,
                            'data': data
                        }
                        return JsonResponse(result, safe=False)
                    else:
                        connections['default'].commit()
                        return JsonResponse({'message': 'SQL executed successfully'})
            except Exception as e:
                return JsonResponse({'error': str(e)}, status=500)
        except json.JSONDecodeError as e:
            return JsonResponse({'error': f"JSON decode error: {str(e)}"}, status=400)
        except Exception as e:
            return JsonResponse({'error': f"Error: {str(e)}"}, status=500)
    return JsonResponse({'error': 'Invalid request method'}, status=405)



def is_valid_sql(sql):
    pattern = re.compile(r"""
            ^\s*                # 开始，允许前导空格
            SELECT              # 关键字SELECT
            \s+                 # 至少一个空格
            (.+?)               # 选择的列名，捕获为任意字符序列，非贪婪模式
            \s+FROM\s+          # 关键字FROM
            (\w+)               # 表名，捕获为一个单词字符序列
            (\s+WHERE\s+.*)?    # 可选的WHERE子句
            \s*;?\s*$           # 结束，允许尾随空格和分号
        """, re.VERBOSE)

    match = pattern.match(sql)
    if match:
        allowed_columns = {
            'student_info': {'*', 'student_id', 'name', 'gender', 'grade', 'major', 'phone_number'},
            'courses': {'*', 'course_id', 'course_name', 'course_description'},
            'student_grades': {'*', 'grade_id', 'student_id', 'course_id', 'grade', 'semester'}
        }
        table_name = match.group(2)
        if table_name not in allowed_columns:
            return False

        selected_columns = set(col.strip() for col in match.group(1).split(','))  # 用户选择的列名集合
        if not selected_columns.issubset(allowed_columns[table_name]):
            return False

        return True
    else:
        return False

def charts(request):
    return render(request, 'charts.html')

def draw_chart(request):
    return render(request, 'draw_chart.html')

